﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetBBSBoard]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetBBSBoard];
GO
CREATE PROCEDURE [dbo].[sproc_GetBBSBoard] 
    @catalog_id int,    
    @staff_name nvarchar(50)=''
/*

====================================================
功能:    得到个人的分类栏的版块
参数:
    @catalog_id int            :    分类栏ID
    @staff_name nvarchar(50)=''    :    员工姓名
    
====================================================

*/
AS
SET NOCOUNT ON
DECLARE @Staff_ID int
SET @Staff_ID =0

--得到员工的ID
SELECT @Staff_ID = Staff_ID 
    FROM uds_staff
    WHERE staff_name = @staff_name

--创建临时表
CREATE TABLE #T(board_id int)

--得到所有公共版块
INSERT INTO #T 
    SELECT board_id 
        FROM uds_bbs_board 
        WHERE board_type =1

--得到所有为成员的版块
INSERT INTO #T 
    SELECT board_id 
            FROM uds_bbs_board 
            WHERE board_id IN
                (
                    SELECT board_id FROM uds_bbs_boardmaster WHERE staff_id =@Staff_ID
                    UNION
                    SELECT board_id FROM uds_bbs_boardmember WHERE staff_id =@Staff_ID
                 )

--显示所有版块信息
SELECT 
    a.board_id,
    a.board_name,
    a.board_description,
    catalog_id,
    (SELECT COUNT(*) 
            FROM uds_bbs_forumitem b
            WHERE b.board_id = a.board_id
    ) as formitems,
    (SELECT COUNT(*) 
            FROM uds_bbs_replay c
            WHERE c.item_id in (SELECT b.item_id 
                            FROM uds_bbs_forumitem b
                            WHERE b.board_id = a.board_id)
    ) as replays,
    e.item_id,
    e.title,
    e.[content],
    e.sender,
    e.send_time
FROM
    uds_bbs_board a
    LEFT OUTER JOIN
    (
        SELECT * 
        from
            dbo.uds_bbs_forumitem m 
        where
            COALESCE(last_replay_time,send_time)
            = (select max(COALESCE(last_replay_time,send_time)) from uds_bbs_forumitem n where m.board_id =n.board_id)
    ) e
    ON a.board_id = e.board_id
WHERE
    a.catalog_id = @catalog_id
    and a.board_id in (select * from #T)

GO